#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d'-1 day' +%F`
fi

ADS_REGION_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE moni.ads_region_ads_report
SELECT dt, province, city, is_invalid_traffic, click_count, impression_count FROM moni.ads_region_ads_report
UNION
SELECT
    '${data_date}' AS dt
    --各省市
    ,if(client_province='0','未知',client_province) AS province
    ,if(client_city='0','未知',client_city) AS city
    ,if(is_invalid_traffic,'正常','异常') AS is_invalid_traffic
    --点击次数和曝光次数
    ,count(if(event_type='click',ad_id,NULL)) AS click_count
    ,count(ad_id) AS impression_count
FROM moni.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY province, city, is_invalid_traffic;


"
ADS_PLATFORM_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE moni.ads_platform_ads_report
SELECT dt, platform_id, platform_name_zh, is_invalid_traffic, click_count, impression_count FROM moni.ads_platform_ads_report
UNION
SELECT
    '${data_date}' AS dt
    ,platform_id
    ,platform_name_zh
    ,if(is_invalid_traffic,'正常','异常') AS is_invalid_traffic
    --点击次数和曝光次数
    ,count(if(event_type='click',ad_id,NULL)) AS click_count
    ,count(ad_id) AS impression_count
FROM moni.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY platform_id,platform_name_zh,is_invalid_traffic;
"

ADS_OS_ADS_REPORT_SQL="
NSERT OVERWRITE TABLE moni.ads_os_ads_report
SELECT dt, client_os_type, is_invalid_traffic, click_count, impression_count FROM moni.ads_os_ads_report
UNION
SELECT
    '${data_date}' AS dt
     --各操作系统
    ,if(client_os_type='0','未知',client_os_type) AS client_os_type
    ,if(is_invalid_traffic,'正常','异常') AS is_invalid_traffic
    --点击次数和曝光次数
    ,count(if(event_type='click',ad_id,NULL)) AS click_count
    ,count(ad_id) AS impression_count
FROM moni.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY client_os_type,
         is_invalid_traffic;
"

ADS_HOUR_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE moni.ads_hour_ads_report
SELECT dt, ad_id, ads_name, hour_str, is_invalid_traffic, click_count, impression_count FROM moni.ads_hour_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,ad_id, ads_name
    ,hour(from_unixtime(event_time/1000)) AS hour_str
    ,if(is_invalid_traffic,'正常','异常') AS is_invalid_traffic
    --点击次数和曝光次数
     ,count(if(event_type='click',ad_id,NULL)) AS click_count
     ,count(ad_id) AS impression_count
FROM moni.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY ad_id, ads_name, hour(from_unixtime(event_time/1000)), is_invalid_traffic;

"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_REGION_ADS_REPORT_SQL}${ADS_PLATFORM_ADS_REPORT_SQL}${ADS_OS_ADS_REPORT_SQL}${ADS_HOUR_ADS_REPORT_SQL}"